Field of the Invention
The present invention relates generally to database recovery and, more particularly, to the creation of a virtual database whereby data recovery is achieved without the need to perform the costly steps associated with a database restore.
Description of the Related Art
In general, a computer database is implemented using files stored in a file system. The content of the database is found entirely within this set of files, also known as Database Data Files. The database can be copied or moved by simply transporting these Data Files. A Database becomes accessible when it is “attached” (or restored), and, accordingly, would be “detached” directly before it is moved again.
Two distinct types of data files are used to implement the database. The first, referred to as the Page Data Files (“PDF,” either primary data files, aka MDF, or secondary data files, aka NDF) contains database pages organized in groups of eight pages called Page Extents. Each database page contains Records (i.e., Rows) which collectively contain all of the data and schema for both the database catalog and for the user.
The second type of data file is called a Log Data File (“LDF”), which contains multiple Virtual Log Files (“VLF”) which in turn contain multiple log blocks. Each log block contains log records which collectively specify the information regarding every modification to the database through time. Sufficient information is stored in these log records to perform physical modifications to the database pages, therefore, enabling movement of the database forward through time during recovery.
In addition, there is enough information available in the log records to perform the physical modifications in reverse, undoing the action of that log record, and hence to move the Database backward through time. Multiple transactions, running simultaneously, serialize all of their physical modifications to the Database through one single continuum, called the Logical Log Sequence (“LLS”). The LLS is a sequential list of Logical Log Files (“LLF”) which are temporarily contained within some VLF within the LDF, and ultimately contained within database backups. LLF and VLF are identical except in regards to the perspective by which they are viewed, the VLF being physical and the LLF being logical.
The PDF and LDF are very important in implementing the database, although in different ways. First, at any one instant in time, all data must be transactionally consistent, meaning that at that instant in time all the work of committed transactions has been applied, and any work of uncommitted transactions which might have been applied are undone. Once database recovery (making consistent the pages with this one instant in time of the LLS) is accomplished, the PDF is all that is needed and there is no further use of the LDF since only one instant in time is being represented.
Second, in regards to the passage of time, the LLS within the LDF is very important. For example, if the complete LLS, starting from the time of the database's creation, was available from backups and/or the live LDF, then all of the database pages within the PDF could be created using only this LLS. In this case, there would be no use of any specific PDF since we can represent them at any instant in time using the LLS.
Database backups are made periodically to save the data contained in both the PDF and LDF. These backups are critical to the correct operation of the database. Databases are typically restored for three primary reasons. First, the entire original data file of the database has been corrupted, lost or deleted. Second, when some data in the tables contained in the data file has been lost or altered in some way. In this case, typically the restore of the backup is not done to the original location, instead the restore is typically performed onto a second standby system and, after the restore is complete, standard Transact Structured Query Language (“TSQL”) can be run against the standby database to extract the correct data which can then be applied against the original database. Third, when an external party (auditors/management or system engineers) wish to see that database data in a state at which it looked at sometime in the past. In this case, the backup is again restored to a standby system and used from there.
Accordingly, there are many different types of backups which are used in different ways during the restore process to bring a database online to a specific Point-in-Time (“PIT”). The most widely used backup types are Full Database Backup, Differential Database Backup, and Transaction Log Backup. A Full Database Backup contains all of the pages from the PDF that were allocated at the time the backup was begun, and the portion of the LLS from the LDF representing all of the required log records up to and including the finish of the backup process.
A Differential Database Backup contains all of the new or modified pages from the PDF since the time of the beginning of the most recent Full Database Backup, and the portion of the LLS from the LDF just the same as in a Full Database Backup. A Transaction Log Backup contains the portion of the LLS from the LDF which have been created since the previous Transaction Log Backup or, i.e., only the newer portion. After a successful backup operation, LLF are identified as no longer being needed and, thus, their corresponding containing VLF is marked appropriately for reuse for any newer LLF created in the future. In all types of backups, when the identified portion of the LLS in the LDF is written to the backup, it is at a granularity of log blocks, and hence most likely will contain an incomplete LLF at the beginning and end of the sequence, bracketing a sequence of complete LLFs in between.
The restore operation is used for several purposes. First, to make a copy of a database for testing or reporting purposes. Second, to recover from the disastrous effect of a mistaken action. Here, if you have an SQL database recovery model set to Full or Bulk-Logged, it is possible to restore from a Full Database Backup plus an optional number of Transaction Log Backups to a point in time. Therefore, in a data loss scenario for instance, you would take a backup of the transaction log or use those made recently, and restore a new copy of the database to a point in time just before the deletion. The missing data could then be copied from the restored copy of the database across to the live database. Third, to recover from a catastrophic failure like a power outage.
Conventional restore operations are costly in terms of both time and disk space. Time is often critical in a disaster-recovery or data-loss scenario. Limited availability of disk space often prevents the creation of necessary copies for testing/reporting and the restoration of some stored procedure or user table which was accidentally deleted. When using RESTORE for either of the first two purposes mentioned above, the time and space required to restore an entire database seems very wasteful. A copy of a database used for testing and/or reporting is only needed temporarily. Moreover, only a fraction of the entire database is actually required in a data loss scenario (e.g., usually a few tables or textual objects, or even just a single page).
It is important to understand the underlying steps taken during a restore operation. Consider the RESTORE DATABASE command used with a Full Database Backup, which can be used to either replace an existing set of data files for the specified Database using the “WITH REPLACE” clause, or it can newly create them. First, all the Page data (allocated Pages) within the Backup is copied to the PDF. Second, all of the Log Block data (i.e., LLS segments) within the Backup, is copied to their corresponding VLF within the LDF. Third, important bootstrapping information found within the configuration information in the Backup and other general bootstrapping information is written to the File Header Page of each Data File, and the Boot Page of the Primary PDF (MDF). Fourth, if the RESTORE command has the “WITH MOVE” clause, then pages in the MDF which are owned by the “sysfiles1” Table are modified and written to reflect the new path names of the Data Files. Finally, the RECOVERY phase is performed. During this restore process, most all of the time is spent in performing the copy phase, step 1 listed above. Accordingly, for large databases, it can take several hours to perform a restore operation.
It is important to understand the recovery phase performed as part of the restore operation, and to note that it is also performed as part of the attach operation. Recovery is performed as the final step in restoring a database from backups, when a database is attached or when the server starts (for example, after a crash). Recovery ensures that a database is transactionally consistent prior to bringing it online. As previously mentioned, if a database is transactionally consistent, all committed work is present and any uncommitted work has been undone.
In Microsoft® SQL Server™, recovery is based on write-ahead logging, which guarantees that changes are written to the log before the corresponding data pages are written. The log always defines the correct view of the database. Simply put, recovery is the process of making the data consistent with the transaction log at a given point in time.
It is also possible to perform a sequence of restore operations each using different backups to restore to a PIT. When there are multiple restore operations, all except the last is specified with the “WITH NORECOVERY” clause. The sequence must begin with a restore using a Full Database Backup, followed by an optional restore using a Differential Database Backup, and then a sequence of restores using Transaction Log Backups. The Full Database Backup will be the most recent previous one to the desired PIT. If present, the Differential Database Backup will be the most recent previous one to the PIT after the Full Database Backup has been used. The Transaction Log Backups will be in ascending chronological order up to the specified PIT, and starting from the Full (or Differential if present) Database Backup.
Several software products have been released surrounding backup and restore functionality by a handful of different software companies. These products offer a variety of mechanisms to utilize compression, which helps to reduce both the space and time required by backup and restore operations, and encryption, which helps to make the data secure.
Most of these backup/restore products also offer a feature which attempts to alleviate or eliminate the space and/or time required in a data loss scenario, and are offered as an alternative to performing a restore of the entire database just to extract some lost data or schema. The current vernacular used in the industry to reference this feature is Object-Level Restore or Object-Level Recovery (“OLR”). This name piggy-backs nicely to the concept of the File-Level Restore which has a granularity lower than a Database-Level Restore. The Object-Level then has a granularity lower than the File-Level. These objects can be either Tables (or Rows) containing actual data, or Schema which are textual objects containing the Data Definition Language (“DDL”) subset of the TSQL language.
Currently, conventional OLR products widely used suffer from a major design flaw which severely limits their usefulness, and renders them generally unsuitable for their intended purpose. These tools only process the PDF data from the backup, and do not perform the proper recovery process using the LDF data from the backup (LLS segment). As such, the resulting data is highly suspect since the pages are not transactionally consistent. Therefore, these products will either crash, fail in mysterious ways, or quietly produce inconsistent data, i.e. garbage data. The OLR tools can only be used reliably in a very limited scope; if the backups being processed were produced while the database is offline, or in a single-user scenario where the operator knows that the database is quiescent for the duration of the backup process.
Moreover, some conventional backup/restore products offer the ability to query a Full Database Backup with SQL SELECT statements executed through Extended Stored Procedures or an Object Linking and Embedding Database Provider. However, these seemingly super-cool features are built using the faulty technology mentioned above, and hence are not generally useful because they unknowingly produce corrupted data.
It seems this faulty OLR and backup query technology could be fixed by having the software perform the necessary redo/undo of each page being referenced. However, in reality, this is an almost impossible task, due to its enormous complexity and the fact that these processes are undocumented and proprietary to the database management system (“DBMS”) vendor.
Some DBMS software, such as Oracle's Flashback™ technology, offer a built-in mechanism within the DBMS, complete with additional SQL syntax, to support the ability to query databases specifying varying points in time from the past. This is implemented using a cache of rollback segments that are maintained specially by the user, then allowing the DBMS Engine to perform redo/undo (roll-forward and roll-back) on any loaded page during the query process. The disadvantage to such systems is the additional setup and configuration required. Also, the time range is limited by the size of the rollback segments.
Some DBMS software vendors offer a built-in online restore mechanism which also is insufficient because it is at the File or Filegroup Level and the data in those Files during this type of restore is unavailable. Not to mention overcoming the daunting task of reorganizing the entire database into multiple filegroups in some manner, which may or may not be beneficial from the online (but unavailable) restore.
One example of the before-mentioned systems is the Quest Software's Litespeed™ product where a special query tool has been written to expose the contents of a backup file. Simple, limited TSQL type queries can be written to extract data from the backup file and before that data is returned any transactions associated with it are applied. The disadvantage to this system is that it is not a database as recognized by the Microsoft SQL Server DBMS itself, and, hence, queries can only be run through a bespoke query tool. None of the conventional standard or third party tools, or applications can be used to extract information from the database.
Hyperbac Technologies' SQL Virtual Restore sends restore command to the SQL Server. When SQL Server is going through the copy phase of the restore, the Hyperbac SQL Virtual Restore intercepts the calls and writes it out to virtual files. SQL Server then does the recovery phase, where Hyperbac SQL Virtual Restore writes out the changes to the cache files. Subsequent requests for data are retrieved from the cache file or the backup. However, the disadvantage to this system is that during the copy phase of the restore, all of the pages in the backup file must be read, which is a time consuming process.
In view of the foregoing, there is a need in the art for, amongst others, a cost effective, reliable database recovery method using the LDF data from the backup, whereby the backup file's contents are immediately available for use by a server without the copy phase of a restore (where the PDF and LDF are first copied from the backup file) first having to take place.